package com.gitee.gen.auto.sql;

import java.io.IOException;
import java.io.InputStream;
import java.net.URL;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;

import cn.hutool.core.collection.CollUtil;

public class AutoTableXlsxUtils {

	private static final Logger log = LoggerFactory.getLogger(AutoTableXlsxUtils.class);

	/**
	 * 获取workbook
	 * 
	 * @param string
	 * @return
	 * @throws IOException
	 */
	public static XSSFWorkbook getXSSFWorkbook(String fileName) throws IOException {
		URL url = AutoTableXlsxUtils.class.getClassLoader().getResource(fileName);
		InputStream input = url.openStream();
		// 读取后缀为“xlsx”的excel文件代码 （“xls”不适用，“xls”得用 HSSFWorkbook）
		XSSFWorkbook wb = new XSSFWorkbook(input);
		return wb;
	}
	
	
	/**
	 * 获取workbook
	 * 
	 * @param string
	 * @return
	 * @throws IOException
	 */
	public static XSSFWorkbook getXSSFWorkbook(InputStream input) throws IOException {
		// 读取后缀为“xlsx”的excel文件代码 （“xls”不适用，“xls”得用 HSSFWorkbook）
		XSSFWorkbook wb = new XSSFWorkbook(input);
		return wb;
	}

	/**
	 * 获取table脚本对象
	 * 
	 * @param wb
	 * @return
	 */
	public static List<TableScriptDO> getTableScript(XSSFWorkbook wb) {
		List<TableScriptDO> list = new ArrayList<>();
		int sheetCount = wb.getNumberOfSheets();
		for (int i = 0; i < sheetCount; i++) {
			XSSFSheet sheet = wb.getSheetAt(i);
			TableScriptDO tableScriptDO = buildTableScriptFromSheet(sheet);
			if (tableScriptDO == null) {
				continue;
			}
			list.add(tableScriptDO);
		}
		return list;
	}

	/**
	 * 
	 * @param sheet
	 * @return
	 */
	private static TableScriptDO buildTableScriptFromSheet(XSSFSheet sheet) {
		if ("目录".equals(sheet.getSheetName())) {
			return null;
		}
		// 获取表名
		XSSFRow tableNameRow = sheet.getRow(1);
		XSSFCell tableNameCell = tableNameRow.getCell(2);
		String tableName = tableNameCell.getStringCellValue();
		if (StringUtils.isEmpty(tableName)) {
			return null;
		}
		TableScriptDO tableScriptDO = new TableScriptDO();
		tableScriptDO.setTableName(tableName);
		// 获取表注释
		XSSFRow tableCommentRow = sheet.getRow(2);
		XSSFCell tableCommentCell = tableCommentRow.getCell(0);
		String tableComment = tableCommentCell.getStringCellValue();
		if (StringUtils.isEmpty(tableComment)) {
			tableComment = sheet.getSheetName();
		}
		tableScriptDO.setTableComment(tableComment);
		List<ColumnScriptDO> columnScriptDOs = processColumnScript(sheet);
		if (CollUtil.isEmpty(columnScriptDOs)) {
			return null;
		}
		tableScriptDO.setColumnScriptDOs(columnScriptDOs);
		return tableScriptDO;
	}

	private static List<ColumnScriptDO> processColumnScript(XSSFSheet sheet) {
		int lastRowNum = sheet.getLastRowNum()+1;
		List<ColumnScriptDO> list = new ArrayList<>();
		for (int startRowNum = 4; startRowNum < lastRowNum; startRowNum++) {
			XSSFRow columnRow = sheet.getRow(startRowNum);
			XSSFCell columnNameCell = columnRow.getCell(1);
			XSSFCell columnCommentCell = columnRow.getCell(2);
			XSSFCell primaryKeyNameCell = columnRow.getCell(3);
			XSSFCell autoIncreaseCell = columnRow.getCell(4);
			XSSFCell typeCell = columnRow.getCell(5);
			XSSFCell lengthCell = columnRow.getCell(6);
			XSSFCell canNullCell = columnRow.getCell(7);
			XSSFCell defaultValueCell = columnRow.getCell(8);

			ColumnScriptDO columnScriptDO = new ColumnScriptDO();
			// 字段名
			String columnName = columnNameCell.getStringCellValue();
			if (StringUtils.isEmpty(columnName)) {
				log.info("columnName is empty");
				continue;
			}
			columnScriptDO.setColumnName(columnName.toLowerCase());
			// 字段注释
			columnScriptDO.setColumnComment(columnCommentCell.getStringCellValue());
			// 是否主键
			String primaryKeyName = primaryKeyNameCell.getStringCellValue();
			if (StringUtils.isNotEmpty(primaryKeyName)) {
				primaryKeyName = primaryKeyName.trim().toLowerCase();
			}
			columnScriptDO.setPrimaryKeyName(primaryKeyName);
			// 是否自增
			String autoIncrease = autoIncreaseCell.getStringCellValue();
			if (StringUtils.isNotEmpty(autoIncrease) && "y".equals(autoIncrease.trim().toLowerCase())) {
				columnScriptDO.setAutoIncrease(true);
			}
			// 字段类型
			String type = typeCell.getStringCellValue();
			if (!checkTypeVaild(type)) {
				log.info("type is not vaild");
				continue;
			}
			columnScriptDO.setType(type.trim().toLowerCase());
			// 长度
			String length = getCellValue(lengthCell);
			if (StringUtils.isEmpty(length)) {
				length = "";
			}
			length = length.trim();
			columnScriptDO.setLength(length);
			// 是否可以为空
			String canNull = canNullCell.getStringCellValue();
			if (StringUtils.isNotEmpty(canNull) && "y".equals(canNull.trim().toLowerCase())) {
				columnScriptDO.setCanNull(true);
			}
			// 默认值
			String defaultValue = getCellValue(defaultValueCell);
			if (StringUtils.isEmpty(defaultValue)) {
				defaultValue = "";
			}
			defaultValue = defaultValue.trim();
			columnScriptDO.setDefaultValue(defaultValue);

			list.add(columnScriptDO);
		}
		return list;
	}

	private static boolean checkTypeVaild(String type) {
		if (StringUtils.isEmpty(type)) {
			return false;
		}
		// TODO 其他类型判断

		return true;
	}

	public static String getCellValue(Cell cell) {
		String cellValue = "";
		if (cell == null) {
			return cellValue;
		}
		// 把数字当成String来读，避免出现1读成1.0的情况
		if (cell.getCellType() == CellType.NUMERIC) {
			cell.setCellType(CellType.STRING);
		}
		// 判断数据的类型
		switch (cell.getCellType()) {
		case NUMERIC: // 数字
			cellValue = String.valueOf(cell.getNumericCellValue());
			break;
		case STRING: // 字符串
			cellValue = String.valueOf(cell.getStringCellValue());
			break;
		case BOOLEAN: // Boolean
			cellValue = String.valueOf(cell.getBooleanCellValue());
			break;
		case FORMULA: // 公式
			cellValue = String.valueOf(cell.getCellFormula());
			break;
		case BLANK: // 空值
			cellValue = "";
			break;
		case ERROR: // 故障
			cellValue = "非法字符";
			break;
		default:
			cellValue = "未知类型";
			break;
		}
		return cellValue;
	}

}
